﻿Imports System.Data.SqlClient

<ComClass(ClsSqHelper.ClassId, ClsSqHelper.InterfaceId, ClsSqHelper.EventsId)>
Public Class ClsSqHelper

#Region "COM GUID"
    ' 这些 GUID 提供此类的 COM 标识 
    ' 及其 COM 接口。若更改它们，则现有的
    ' 客户端将不再能访问此类。
    Public Const ClassId As String = "d32df843-039f-406e-a410-309b4e951324"
    Public Const InterfaceId As String = "d007f319-aeda-4b59-8eee-40fd3e6268a3"
    Public Const EventsId As String = "4779eb13-4ac6-4b40-ac17-5a41b2d4a576"
#End Region

    ' 可创建的 COM 类必须具有一个不带参数的 Public Sub New() 
    ' 否则， 将不会在 
    ' COM 注册表中注册此类，且无法通过
    ' CreateObject 创建此类。
    Public Sub New()
        MyBase.New()
    End Sub

    Private _conStr As String
    Public Property ConString() As String
        Get
            Return _conStr
        End Get
        Set(ByVal value As String)
            _conStr = value
        End Set
    End Property

    Public Function ExcuteDataReader(ByVal strSelectCmd As String) As Object
        Dim conn As SqlConnection = Nothing
        Dim sdr As SqlDataReader
        Try
            conn = New SqlConnection(Me.ConString)
            Dim cmd As New SqlCommand(strSelectCmd, conn)
            conn.Open()
            sdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)

        Catch ex As Exception
            conn.Dispose()
            Throw ex
        End Try
        Dim rtn As New List(Of String())
        If sdr.HasRows Then
            While sdr.Read()
                Dim curRowValues(0 To sdr.FieldCount - 1) As String

                For index = 0 To sdr.FieldCount - 1
                    curRowValues(index) = sdr.GetName(index) & ":" & sdr.GetValue(index)
                Next
                rtn.Add(curRowValues)
            End While
        End If
        Return rtn.ToArray()

    End Function

    Public Function ExcuteDataTable(ByVal strSelectCmd As String, ByVal ParamArray paras As SqlParameter()) As DataTable
        Using conn As SqlConnection = New SqlConnection(Me.ConString)
            Dim da As New SqlDataAdapter(strSelectCmd, conn)
            da.SelectCommand.Parameters.AddRange(paras)
            Dim dt As New DataTable
            da.Fill(dt)
            Return dt
        End Using
    End Function

    Public Function ExcuteNonQuery(ByVal strCmd As String, ByVal ParamArray paras As SqlParameter()) As Integer
        Using conn As SqlConnection = New SqlConnection(Me.ConString)
            Dim cmd As New SqlCommand(strCmd, conn)
            cmd.Parameters.AddRange(paras)
            conn.Open()
            Return cmd.ExecuteNonQuery
        End Using
    End Function

    Public Function ExcuteScalar(ByVal strSelectCmd As String, ByVal ParamArray paras As SqlParameter()) As Object
        Using conn As SqlConnection = New SqlConnection(Me.ConString)
            Dim cmd As New SqlCommand(strSelectCmd, conn)
            cmd.Parameters.AddRange(paras)
            conn.Open()
            Return cmd.ExecuteScalar
        End Using
    End Function



End Class


